/* ExcelInoutToDB.java
 *
 * Project/Product: GW
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   Administrator	2008-2-20	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2008
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package system.imp;

import java.io.DataInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;

import system.base.ClientDownupfileInfo;
import system.base.InoutType;
import system.base.WarehouseCategory;
import framework.core.*;

public class ExcelInoutTypeToDB implements GWBaseProcesser {
    public static String doSaveToDB(DataInputStream aFile, DBConnection con, ILoginContext loginContext, String tableName, String warehouseCode,String upDownType)
            throws GWException

    {
        int tSuccCount = 0;
        int tFailCount = 0;
        String tFailFile = "0";
        HSSFWorkbook tHSSFWorkbook = new HSSFWorkbook();
        // 开始读取excel文件
        HSSFSheet tHSSFSheet = null;
        try {
            tHSSFWorkbook = new HSSFWorkbook(aFile);
            tHSSFSheet = tHSSFWorkbook.getSheet(tableName);
        } catch (IOException e) {
            e.getStackTrace();
            new Exception("批量下载文件失败");
        }
        ArrayList alist = new ArrayList();
        ArrayList tsucceslist = new ArrayList();
        ArrayList tErrList = new ArrayList();

        // 读取并检查Excel明细
        int tRowCount = parseExcelInoutType(alist, tHSSFSheet, loginContext);
        if (alist.size() > 0) {
            // 保存上传文件到数据库
            for (int i = 0; i < alist.size(); i++) {
                try {
                    GWManufactureUploadVO gwManufactureUpload = (GWManufactureUploadVO) alist.get(i);

                    InoutType iot = gwManufactureUpload.getInoutType();

                    // 判断数据库中是否存在该条记录
                    // 根据仓库编码和分类编码得到是否在数据库中存在该条信息
                    /*
                     * Conditions cs = new Conditions();
                     * cs.add(WarehouseCategory.WAREHOUSE_CODE,wc.getWarehouseCode());
                     * cs.add(WarehouseCategory.CATEGORY_CODE,wc.getCategoryCode());
                     * WarehouseCategory warehouseCategory =
                     * WarehouseCategory.fetch(con,cs);
                     */

                    InoutType inoutType = InoutType.fetch(con, iot.getId());
                    if (inoutType == null) {
                        // 数据库中不存在的操作:得到ID = GUID值
                        inoutType = InoutType.fetch();
                    }

                    // 更新当前一条Excel信息数据库
                    inoutType.setCode(iot.getCode());
                    inoutType.setName(iot.getName());
                    inoutType.setDisplayOrder(iot.getDisplayOrder());
                    inoutType.setInoutType(iot.getInoutType());
                    inoutType.setRemark(iot.getRemark());
                    inoutType.setCreateUserId(iot.getCreateUserId());
                    inoutType.setCreateUser(iot.getCreateUser());
                    inoutType.setCreateTime(iot.getCreateTime());
                    inoutType.setLastUpdateUserId(iot.getLastUpdateUserId());
                    inoutType.setLastUpdateUser(iot.getLastUpdateUser());
                    inoutType.setLastUpdateTime(iot.getLastUpdateTime());
                    inoutType.save(con);
                    tSuccCount++;
                } catch (Exception e) {
                    System.out.println(e.getMessage());
                    tFailCount++;
                }
            }
                        
            // 在循环结束后在客户上传下载表中记录操作信息
            try {
                ClientDownupfileInfo cdfi = ClientDownupfileInfo.fetch();
                cdfi.setCreateTime(new framework.core.DateUtil().getFullString());
                cdfi.setCreateUser(loginContext.getLoginUserName());
                cdfi.setCreateUserId(loginContext.getLoginUserId());
                cdfi.setDownloadtyp(upDownType);
                cdfi.setLastUpdateTime(new framework.core.DateUtil().getFullString());
                cdfi.setLastUpdateUser(loginContext.getLoginUserName());
                cdfi.setLastUpdateUserId(loginContext.getLoginUserId());
                cdfi.setParmsstr(tableName);
                cdfi.setDatatableName(tableName);
                cdfi.setParmstype(ClientDownupfileInfo.UPDOWN_STATE_TEXT);
                cdfi.setStatus(ConstDefine.STR_YES);
                cdfi.setUpdownState(ClientDownupfileInfo.UPDOWN_STATE_SUCCESS);
                cdfi.setWarehouseUincode(warehouseCode);
                cdfi.save(con);
            } catch (Exception e) {
                System.out.println(e.getMessage());    
            }

        }
        // 读取sheet表单
        return tSuccCount + "-" + tFailCount + "-" + tRowCount;
    }

    // end chengbiao.he

    /* start chengbiao.he */
    // 解析Excel文件
    private static int parseExcelInoutType(ArrayList aList, HSSFSheet aHSSFSheet, ILoginContext loginContext) throws GWException {
        int i = 0;
        // 取得第一行的对象
        HSSFRow tFirstRow = aHSSFSheet.getRow(0);
        
        DecimalFormat a = new DecimalFormat("#");
        while (true) {
            HSSFRow tHSSFRow = aHSSFSheet.getRow(1 + i);
            boolean flag = false;
            try {

                for (int j = 0; j < 12; j++) {
                    if (tHSSFRow == null) {
                        break;
                    }
                    if (tHSSFRow.getCell((short) j) == null) {
                        break;
                    }
                    flag = true;
                    break;
                }
                if (!flag) {
                    break;
                } else {
                    GWManufactureUploadVO tGSManufactureUploadVO = new GWManufactureUploadVO();
                    int tResult = 1;
                    String tFailReason = "";

                    InoutType iot = new InoutType();

                    for (int j = 0; j <12; j++) {
                        // 获得EXCEL首行记录
                        String tFiledName = tFirstRow.getCell((short) j).getStringCellValue();
                        // 序号
                        if (tFiledName.equalsIgnoreCase(InoutType.ID)) {
                            String tID = PoiUtil.getCellValue(tHSSFRow, j, "");// String.valueOf(tHSSFRow.getCell((short)j).getStringCellValue());
                            iot.setId(tID);
                        }
                        // 编码
                        else if (tFiledName.equalsIgnoreCase(InoutType.CODE)) {
                            String tCode = PoiUtil.getCellValue(tHSSFRow, j, ""); // tHSSFRow.getCell((short)j).getStringCellValue().trim();
                            iot.setCode(tCode);
                        }
                        // 名称
                        else if (tFiledName.equalsIgnoreCase(InoutType.NAME)) {
                            String tName = PoiUtil.getCellValue(tHSSFRow, j, ""); // tHSSFRow.getCell((short)j).getStringCellValue().trim();
                            iot.setName(tName);
                        }
                        // 显示顺序
                        else if (tFiledName.equalsIgnoreCase(InoutType.DISPLAY_ORDER)) {
                            int tDisplayOrder = tHSSFRow.getCell((short)j).getCellType();
                            iot.setDisplayOrder(tDisplayOrder);

                        }
                        // 出入库类别
                        else if (tFiledName.equalsIgnoreCase(InoutType.INOUT_TYPE)) {
                            String tInoutType = PoiUtil.getCellValue(tHSSFRow, j, "");// String.valueOf(tHSSFRow.getCell((short)j).getStringCellValue());
                            iot.setInoutType(tInoutType);
                        }
                        // 描述
                        else if (tFiledName.equalsIgnoreCase(InoutType.REMARK)) {
                            String tRemark = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue().trim();
                            iot.setRemark(tRemark);

                        }
                        // 创建用户序号
                        else if (tFiledName.equalsIgnoreCase(InoutType.CREATE_USER_ID)) {
                            String tCreateUserId = PoiUtil.getCellValue(tHSSFRow, j, ""); // tHSSFRow.getCell((short)j).getStringCellValue();
                            iot.setCreateUserId(tCreateUserId);
                        }

                        // 创建用户
                        else if (tFiledName.equalsIgnoreCase(InoutType.CREATE_USER)) {
                            String tCreateUser = PoiUtil.getCellValue(tHSSFRow, j, "");// String.valueOf(tHSSFRow.getCell((short)j).getNumericCellValue());
                            iot.setCreateUser(tCreateUser);
                        }
                        // 单证创建时间
                        else if (tFiledName.equalsIgnoreCase(InoutType.CREATE_TIME)) {
                            String tCreateTime = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                            iot.setCreateTime(tCreateTime);
                        }
                        // 最后修改人序号
                        else if (tFiledName.equalsIgnoreCase(InoutType.LAST_UPDATE_USER_ID)) {
                            String tLastUpdateUserId = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                            iot.setLastUpdateUserId(tLastUpdateUserId);
                        }
                        // 最后修改人
                        else if (tFiledName.equalsIgnoreCase(InoutType.LAST_UPDATE_USER)) {
                            String tLastUpdateUser = PoiUtil.getCellValue(tHSSFRow, j, ""); // tHSSFRow.getCell((short)j).getStringCellValue().trim();
                            iot.setLastUpdateUser(tLastUpdateUser);
                        }
                        // 最后修改时间
                        else if (tFiledName.equalsIgnoreCase(InoutType.LAST_UPDATE_TIME)) {
                            String tLastUpdateTime = PoiUtil.getCellValue(tHSSFRow, j, "");// tHSSFRow.getCell((short)j).getStringCellValue();
                            iot.setLastUpdateTime(tLastUpdateTime);
                        }

                    }
                    tGSManufactureUploadVO.setInoutType(iot);
                    // tGSManufactureUploadVO.setFailure_reason(tFailReason);
                    tGSManufactureUploadVO.setInsert_result(String.valueOf(tResult));
                    tGSManufactureUploadVO.setRowNumber(i + 1);
                    aList.add(tGSManufactureUploadVO);
                    i++;
                }
            } catch (RuntimeException e) {
                LogWriter.error("****Manufacture BatchImport RuntimeEx :[" + e.getMessage() + "]");
                throw new GWException("导入数据中某一列数据类型不符合规定.");
            } catch (Error e) {
                LogWriter.error("****Manufacture BatchImport ErroeEx :[" + e.getMessage() + "]");
                throw new GWException(e.getMessage());
            } catch (Exception e) {
                LogWriter.error("****Manufacture BatchImport Ex :[" + e.getMessage() + "]");
                throw new GWException(e.getMessage());
            }
        }
        return i ;
    }
    
    //根据参数创建要下载的Excel文件
    /**
     * return 创建文件的路径
     * 创建失败返回 "Error"
     */
    public static String createExcelByDownLoadInoutType(DBConnection con,String serverPath,String tableName,String warehouseCode,String type,String lastupdatetime)
    {
        String strFilePath = "";
        try{
            //根据参数查询数据
            Conditions cs = new Conditions();
            if (type.equals(ConstDefine.EXCELFILE_LOAD_TYPE_TIME))
            {
                String strDate = framework.core.DateUtil.formatToLongDateString(lastupdatetime, new framework.core.DateUtil().getFullString());
                cs.add(WarehouseCategory.LAST_UPDATE_TIME +">= ?",Conditions.CUSTOM,strDate);
            }
            ObjectList list = InoutType.fetch().search(con, cs);            
            
            //Excel生成文件保存到指定位置
            try{    
                HSSFWorkbook tWb = new HSSFWorkbook();
                HSSFSheet tSheet = tWb.createSheet(tableName);
                HSSFRow tRow = null;
           
                //表头行
                tRow = tSheet.createRow((short)0);

                HSSFCell c1 = PoiUtil.createCell(tWb,tRow,(short)0,InoutType.ID);
                HSSFCell c2 = PoiUtil.createCell(tWb,tRow,(short)1,InoutType.CODE);
                HSSFCell c3 = PoiUtil.createCell(tWb,tRow,(short)2,InoutType.NAME);
                HSSFCell c4 = PoiUtil.createCell(tWb,tRow,(short)3,InoutType.DISPLAY_ORDER);
                HSSFCell c5 = PoiUtil.createCell(tWb,tRow,(short)4,InoutType.INOUT_TYPE);
                HSSFCell c6 = PoiUtil.createCell(tWb,tRow,(short)5,InoutType.REMARK);
                HSSFCell c7 = PoiUtil.createCell(tWb,tRow,(short)6,InoutType.CREATE_USER_ID);
                HSSFCell c8 = PoiUtil.createCell(tWb,tRow,(short)7,InoutType.CREATE_USER);
                HSSFCell c9 = PoiUtil.createCell(tWb,tRow,(short)8,InoutType.CREATE_TIME);
                HSSFCell c10 = PoiUtil.createCell(tWb,tRow,(short)9,InoutType.LAST_UPDATE_USER_ID);
                HSSFCell c11 = PoiUtil.createCell(tWb,tRow,(short)10,InoutType.LAST_UPDATE_USER);
                HSSFCell c12 = PoiUtil.createCell(tWb,tRow,(short)11,InoutType.LAST_UPDATE_TIME);
     
                short index = 0 ; //当前所在行号          
                short index_b = 3;
                for(int i=0;i<list.size();i++){
                    InoutType iot = (InoutType)list.get(i);    
          
                    //创建一行
                    tRow = tSheet.createRow((short)(index+1));
                    
                    PoiUtil.createCell(tWb,tRow,(short)0,iot.getId() + "");
                    PoiUtil.createCell(tWb,tRow,(short)1,iot.getCode() + "");
                    PoiUtil.createCell(tWb,tRow,(short)2,iot.getName() + "");
                    PoiUtil.createCell(tWb,tRow,(short)3,iot.getDisplayOrder() + "");
                    PoiUtil.createCell(tWb,tRow,(short)4,iot.getInoutType() + "");
                    PoiUtil.createCell(tWb,tRow,(short)5,iot.getRemark() + "");
                    PoiUtil.createCell(tWb,tRow,(short)6,iot.getCreateUserId() + "");
                    PoiUtil.createCell(tWb,tRow,(short)7,iot.getCreateUser() + "");
                    PoiUtil.createCell(tWb,tRow,(short)8,iot.getCreateTime() + "");
                    PoiUtil.createCell(tWb,tRow,(short)9,iot.getLastUpdateUserId() + "");
                    PoiUtil.createCell(tWb,tRow,(short)10,iot.getLastUpdateUser() + "");
                    PoiUtil.createCell(tWb,tRow,(short)11,iot.getLastUpdateTime() + "");

                    index++;
                }
                String strTime = "";
                try{
                    strTime = system.imp.DateUtil.getCurrTime();
                }
                catch(Exception e)
                {
                }
                
                String fileName;
                String filePath = serverPath + ConstDefine.EXCELFILE_SAVECREATEFILE_PATH;
                fileName ="download_" + strTime + "_" + tableName + ".xls";
                FileOutputStream fileOut = new FileOutputStream(filePath+fileName);
                tWb.write(fileOut);
                
                //返回下载路径
                strFilePath = ConstDefine.EXCELFILE_DOWNLOADCREATEFILE_PATH + fileName;
                
            }catch(Exception ex){
                strFilePath = ConstDefine.EXCELFILE_CREATE_ERROR;
            }
            
        }
        catch(Exception e)
        {
            strFilePath = ConstDefine.EXCELFILE_CREATE_ERROR;
        }
        
        return strFilePath;
    }    
}
